package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.BookingDTO;
import model.UserDTO;
import dao.BookingDAO;
import dao.DAOException;

public class BookingDAOImpl implements BookingDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/facilitybookingsystem";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
     
	
	public void  insertBooking(BookingDTO booking) throws DAOException

	{
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String insertSql = "INSERT INTO booking (UserID,FacilityID,Startdate,Period,Bstatus,Priority,StaffReason) "
				+ "VALUES('"
				+ booking.getUserID()
				+ "', '"
				+ booking.getFacilityID()
				+ "', '"
				+ df.format(booking.getStartDate())
				+ "', '"
				+ booking.getPeriod()
				+ "', '"
				+ booking.getStatus()
				+ "', '"+booking.getPriority()+"','" + booking.getReason() + "');";
		
	//	"
	//	+ booking.getPriority() + "'

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void updateBooking(BookingDTO booking) throws DAOException {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String status = "updated";
		String updateSql = "UPDATE booking SET FacilityID = '"
				+ booking.getFacilityID() + "',Period = '" + booking.getPeriod()
				+ "',Priority = '"+ booking.getPriority()
				 + "',Startdate ='" + df.format(booking.getStartDate())+"',BStatus = '" + status + "',StaffReason = '" + booking.getReason()
				+ "' WHERE BookingID='" + booking.getBookingID() + "';";
		System.out.println(updateSql);
		
		/*"'AND UserID='"+booking.getUserID()+*/
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void cancelBooking(BookingDTO booking) throws DAOException {
		String status = "cancelled";
		String cancelSql = "UPDATE booking SET BStatus ='" + status
				+ "' WHERE BookingID = '" + booking.getBookingID() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing cancel: " + cancelSql);
			int rows = st.executeUpdate(cancelSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void deleteBooking(BookingDTO booking) throws DAOException {
		String status = "deleted";
		String deleteSql = "UPDATE booking SET BStatus = '" + status
				+ "' WHERE BookingID='" + booking.getBookingID() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			int rows = st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public ArrayList<BookingDTO> findAllBookingByUser(UserDTO user)
			throws DAOException {

		ArrayList<BookingDTO> items = new ArrayList<BookingDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		/*
		 * String selectSql =
		 * "SELECT BookingID, FacilityID, Period, Priority, StaffReason, MgrComments FROM booking WHERE UserID = "
		 * + user.getUserID() + ";";
		 */
		String selectSql = "SELECT BookingID, FacilityID, Startdate, BStatus, Period, Priority, StaffReason, MgrComments FROM booking b,user u WHERE b.UserID = u.UserID AND u.Username = '"
				+ user.getUserName()
				+ "'AND b.BStatus IN ('approved','rejected','cancelled','deleted')";

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				BookingDTO book = new BookingDTO();
				book.setBookingID(Integer.parseInt(rs.getString("BookingID")));
				book.setFacilityID(Integer.parseInt(rs.getString("FacilityID")));
				book.setStatus(rs.getString("BStatus"));
				book.setStartDate(rs.getDate("Startdate"));
				book.setPeriod(Integer.parseInt(rs.getString("Period")));
				book.setPriority(rs.getString("Priority"));
				book.setReason(rs.getString("StaffReason"));
				book.setComment(rs.getString("MgrComments"));
				items.add(book);
			}
			st.close();

		} catch (Exception e) {
			String error = "Error selecting booking. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public ArrayList<BookingDTO> findBookingByUserAndStatus(UserDTO user)
			throws DAOException {

		ArrayList<BookingDTO> bookUserStatusItems = new ArrayList<BookingDTO>();
		/*
		 * String selectSql =
		 * "SELECT BookingID, FacilityID, Period, Priority, StaffReason, MgrComments FROM Booking WHERE UserID = '"
		 * + user.getUserID() + " AND (BStatus <> 'Approved' OR 'Rejected')";
		 */

		String selectSql = "SELECT BookingID, FacilityID,BStatus, Startdate, Period, Priority, StaffReason, MgrComments FROM booking b,user u WHERE b.UserID = u.UserID AND u.Username = '"
				+ user.getUserName()
				+ "' AND b.BStatus NOT IN ('approved','rejected','cancelled','deleted')";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		Connection conn = null;

		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				BookingDTO bookUserStatus = new BookingDTO();
				bookUserStatus.setBookingID(Integer.parseInt(rs
						.getString("BookingID")));
				bookUserStatus.setFacilityID(Integer.parseInt(rs
						.getString("FacilityID")));
				bookUserStatus.setPeriod(Integer.parseInt(rs
						.getString("Period")));
				bookUserStatus.setStartDate(rs.getDate("startDate"));
				bookUserStatus.setPriority(rs.getString("Priority"));
				bookUserStatus.setStatus(rs.getString("BStatus"));
				bookUserStatus.setReason(rs.getString("StaffReason"));
				bookUserStatus.setComment(rs.getString("MgrComments"));
				bookUserStatusItems.add(bookUserStatus);
			}
			st.close();

		} catch (Exception e) {
			String error = "Error selecting booking. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);

		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return bookUserStatusItems;

	}

	public ArrayList<BookingDTO> findAllBooking() throws DAOException {
		ArrayList<BookingDTO> booking = new ArrayList<BookingDTO>();
		return booking;
	}

	public ArrayList<BookingDTO> findAllBookingbyMonth(Date date)
			throws DAOException {
		ArrayList<BookingDTO> booking = new ArrayList<BookingDTO>();
		return booking;
	}

	/*
	 * Created by Lena
	 */
	@Override
	public ArrayList<BookingDTO> findCurrentBooking() throws DAOException {
		Calendar current = Calendar.getInstance();
		int currentmonth = current.get(Calendar.MONTH) + 1;
		ArrayList<BookingDTO> bookinglist = new ArrayList<BookingDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM booking;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				BookingDTO booking = new BookingDTO();
				booking.setUserID(rs.getInt("userID"));
				booking.setBookingID(rs.getInt("bookingID"));
				booking.setFacilityID(rs.getInt("facilityID"));
				booking.setComment(rs.getString("mgrcomments"));
				booking.setPeriod(rs.getInt("period"));
				booking.setPriority(rs.getString("priority"));
				booking.setReason(rs.getString("staffreason"));
				booking.setStartDate(rs.getDate("startDate"));
				booking.setStatus(rs.getString("bstatus"));
				Calendar cal = Calendar.getInstance();
				cal.setTime(booking.getStartDate());
				int m = cal.get(Calendar.MONTH) + 1;
				if (m == currentmonth) {
				bookinglist.add(booking);
				}
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return bookinglist;
	}

	/*
	 * Created by Daniel
	 */
	@Override
	public ArrayList<BookingDTO> FindBookingByStatus(int managerID)
			throws DAOException {

		ArrayList<BookingDTO> bookings = new ArrayList<BookingDTO>();
		String selectQuery = "SELECT * FROM booking Join user On booking.userID=user.userID where BStatus IN ('booked','updated')AND user.ManagerID="
				+ managerID + "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectQuery);
			ResultSet rs = st.executeQuery(selectQuery);
			while (rs.next()) {
				BookingDTO bking = new BookingDTO();
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				bking.setBookingID(Integer.parseInt(rs.getString("BookingID")));

				bking.setUserID(Integer.parseInt(rs.getString("UserID")));

				bking.setFacilityID(Integer.parseInt(rs.getString("FacilityID")));
				bking.setStartDate(df.parse(rs.getString("Startdate")));
				bking.setPeriod(Integer.parseInt(rs.getString("Period")));
				bking.setStatus(rs.getString("BStatus"));
				bking.setPriority(rs.getString("Priority"));
				bking.setReason(rs.getString("StaffReason"));
				bking.setComment(rs.getString("MgrComments"));
				// System.out.println(bking.toString());
				bookings.add(bking);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return bookings;
	}

	public ArrayList<UserDTO> findUserList(UserDTO uid) throws DAOException {

		ArrayList<UserDTO> userList = new ArrayList<UserDTO>();

		return userList;
	}

	/*
	 * Created by Daniel
	 */
	public void approveReject(BookingDTO booking) throws DAOException {
		// TODO Auto-generated method stub

		String UpdateQuery = "Update booking Set Bstatus='"
				+ booking.getStatus() + "' Where BookingID="
				+ booking.getBookingID() + "";
		String UpdateComment = "Update booking Set MgrComments='"
				+ booking.getComment() + "' Where BookingID="
				+ booking.getBookingID() + "";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + UpdateQuery);
			// ResultSet rs = st.executeQuery(UpdateQuery);
			st.executeUpdate(UpdateQuery);
			st.executeUpdate(UpdateComment);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn = DriverManager.getConnection(dbUrl, dbUserName,
						dbPassword);
				Statement st = conn.createStatement();
				Logger.getLogger(getClass().getName()).log(Level.INFO,
						"Executing update: " + UpdateQuery);
				// ResultSet rs = st.executeQuery(UpdateQuery);
				st.executeUpdate(UpdateQuery);
				st.close();
			} catch (Exception e) {
				String error = "Error selecting users. Nested Exception is: "
						+ e;
				Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
				throw new DAOException(error);
			} finally {
				try {
					conn.rollback();
					conn.close();
				} catch (Exception e) {

				}

			}
		}
	}

	/*
	 * Created by Daniel
	 */
	public BookingDTO getBooking(int bookID) throws DAOException {

		String selectQuery = "SELECT * FROM booking Where BookingID=" + bookID
				+ "";
		BookingDTO book = new BookingDTO();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectQuery);
			ResultSet rs = st.executeQuery(selectQuery);

			DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

			if (rs.next()) {
				book.setBookingID(Integer.parseInt(rs.getString("BookingID")));
				book.setUserID(Integer.parseInt(rs.getString("UserID")));
				book.setFacilityID(Integer.parseInt(rs.getString("FacilityID")));
				book.setStartDate(df.parse(rs.getString("Startdate")));
				book.setPeriod(Integer.parseInt(rs.getString("Period")));
				book.setStatus(rs.getString("BStatus"));
				book.setPriority(rs.getString("Priority"));
				book.setReason(rs.getString("StaffReason"));
				book.setComment(rs.getString("MgrComments"));
			}

			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);

		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return book;
	}

	@Override
    public ArrayList<BookingDTO> findBookings(int facilityID) throws DAOException
    {
    	String  sql = "SELECT * FROM  booking where FacilityID = '"+ facilityID+"'";

		ArrayList<BookingDTO> booklistbyfacilityid = new ArrayList<BookingDTO>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + sql);
		//	int res = st.executeUpdate(sql);
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				BookingDTO booking = new BookingDTO();
				booking.setUserID(rs.getInt("UserID"));
				booking.setBookingID(rs.getInt("BookingID"));
				booking.setFacilityID(rs.getInt("FacilityID"));
				booking.setComment(rs.getString("MgrComments"));
				booking.setPeriod(rs.getInt("Period"));
				booking.setPriority(rs.getString("priority"));
				booking.setReason(rs.getString("StaffReason"));
				booking.setStartDate(rs.getDate("Startdate"));
				booking.setStatus(rs.getString("BStatus"));
				Calendar cal = Calendar.getInstance();
				cal.setTime(booking.getStartDate());
				
				booklistbyfacilityid.add(booking);
				
			}
			
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return booklistbyfacilityid;
    	
    }
}
